/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package tuitionmanagement.DAO;

import tuitionmanagement.util.PassEncrypt;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import tuitionmanagement.entity.Account;
import tuitionmanagement.util.DBUtil;
import java.sql.CallableStatement;

/**
 *
 * @author Admin
 */
public class AccountDAO {

    private Connection con;
    private PassEncrypt pe;
    private ResultSet rs;
    private CallableStatement cst;
    private PreparedStatement pst;
    private String lastError;
    private final String SQL_CREATE;
    private final String SQL_READ;
    private final String SQL_UPDATE;
    private final String SQL_DELETE;
    private final String SQL_FOR_AUTHENTICATION;
    private final String SQL_CHANGE_PASS;

    /** Creates a new instance of AccountDAO */
    public AccountDAO() {
        con = null;
        pe = new PassEncrypt();
        rs = null;
        pst = null;
        lastError = "";
        SQL_CREATE = "INSERT INTO FeeAccount(AccountId,Username,Password,RoleId) VALUES(?, ?, ?, ?)";
        SQL_READ = "SELECT * FROM FeeAccount";
        SQL_UPDATE = "update FeeAccount set Username=? where AccountId=? ";
        SQL_CHANGE_PASS = "update FeeAccount set Password=? where username=? and Password=?";
        SQL_DELETE = "DELETE FROM FeeAccount WHERE AccountID = ?";
        SQL_FOR_AUTHENTICATION = "SELECT * FROM FeeAccount WHERE Username = ? AND Password = ?";
    }

    /**
     * Get Account list
     * @return ArrayList<Account>
     */
    @SuppressWarnings("static-access")
    public ArrayList<Account> readByAll() {
        ArrayList<Account> list = new ArrayList<Account>();
        con = DBUtil.getConn();
        try {
            pst = con.prepareStatement(SQL_READ);
            rs = pst.executeQuery();
            while (rs.next()) {
                Account b = new Account();
                b.setAccountId(rs.getInt(1));
                b.setRoleId(rs.getInt(2));
                b.setUsername(rs.getString(3));
                b.setPassword(rs.getString(4));

                list.add(b);
            }
        } catch (SQLException ex) {
            setLastError(ex.getMessage());
        }
        return list;
    }

    /**
     * Check whether Account is exist?
     * @param b
     * @return boolean
     */
    public boolean check(String username) {
        ArrayList<Account> list = readByAll();
        for (Account acc : list) {
            if (acc.getUsername().equalsIgnoreCase(username)) {
                setLastError("Existed! Please add another!");
                return false;
            }
        }
        return true;
    }

    /**
     * Add a new Account
     * @param Account
     * @return
     */
    public boolean create(Account acc) {
        if (!acc.validate()) {
            setLastError(acc.getLastError());
            return false;
        }
        if (!check(acc.getUsername())) {
            return false;
        }

        try {
            con = DBUtil.getConn();
            pst = con.prepareStatement(SQL_CREATE);
            pst.setInt(1, acc.getAccountId());
            pst.setString(2, acc.getUsername());
            pst.setString(3, pe.HMD5(acc.getPassword()));
            pst.setInt(4, acc.getRoleId());
            if (pst.executeUpdate() > 0) {
                setLastError("Add successfully!");
                return true;
            } else {
                setLastError("Failed!");
            }

        } catch (SQLException ex) {
            setLastError(ex.getMessage());
        } finally {
            DBUtil.closeConn(con);
        }
        return false;
    }

    /**
     * Update Account information
     * @param b
     * @return
     */
    public boolean update(Account acc) {
        if (!acc.validate()) {
            setLastError(acc.getLastError());
            return false;
        }
        con = DBUtil.getConn();
        try {
            int effectedRows = 0;
            pst = con.prepareStatement(SQL_UPDATE, ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
            pst.setString(1, acc.getUsername());
            pst.setInt(2, acc.getAccountId());
            effectedRows = pst.executeUpdate();
            setLastError("Update Account successfully!");
            if (effectedRows > 0) {
                return true;
            }
        } catch (SQLException ex) {
            setLastError(ex.getMessage());
        } finally {
            DBUtil.closeConn(con);
        }
        return false;
    }

    /**
     * Delete a Account
     * @param username
     * @return
     */
//<<<<<<< .mine
//    public boolean delete(int accId) {
//=======
//    public boolean delete(int accID) {
//>>>>>>> .r85
//        con = DBUtil.getConn();
//        try {
//            pst = con.prepareStatement(SQL_DELETE, ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
//<<<<<<< .mine
//            pst.setInt(1, accId);
//=======
//            pst.setInt(1, accID);
//>>>>>>> .r85
//            if (pst.executeUpdate() > 0) {
//                setLastError("Delete Account successfully!");
//                return true;
//            }
//        } catch (SQLException ex) {
//            setLastError(ex.getMessage());
//        } finally {
//            DBUtil.closeConn(con);
//        }
//        return false;
//    }<<<<<<< .mine
////    public boolean delete(int accId) {
//=======
//    public boolean delete(int accID) {
//>>>>>>> .r85
//        con = DBUtil.getConn();
//        try {
//            pst = con.prepareStatement(SQL_DELETE, ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
//<<<<<<< .mine
//            pst.setInt(1, accId);
//=======
//            pst.setInt(1, accID);
//>>>>>>> .r85

    /**
     * Check for login
     * @param acc
     * @return boolean
     */
    public boolean authencicate(Account acc) {
        con = DBUtil.getConn();
        try {
            pst = con.prepareStatement(SQL_FOR_AUTHENTICATION);
            pst.setString(1, acc.getUsername());
            pst.setString(2, new PassEncrypt().HMD5(acc.getPassword()));
            rs = pst.executeQuery();
            if (rs.next()) {
                setLastError("Login successfully!");
                acc.setAccountId(rs.getInt("AccountId"));
                acc.setRoleId(rs.getInt("RoleID"));
                return true;
            }
        } catch (SQLException ex) {
            setLastError(ex.getMessage());
        }

        setLastError("Please check username and password again!");
        return false;
    }

    /**
     * Charge password for current account
     * @param acc
     * @param oldPass
     * @return boolean
     */
    public boolean changePass(Account acc, String oldPass) {
        con = DBUtil.getConn();
        String pass = pe.HMD5(acc.getPassword());
        String old = pe.HMD5(oldPass);
        try {
            pst = con.prepareStatement(SQL_CHANGE_PASS, ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
            pst.setString(1, pass);
            pst.setString(2, acc.getUsername());
            pst.setString(3, old);
            int eff = pst.executeUpdate();
            pst.close();
            if (eff > 0) {
                return true;
            }
        } catch (SQLException ex) {
            setLastError(ex.getMessage());
        } finally {
            DBUtil.getConn();
        }
        setLastError("Please enter your old password again!");
        return false;
    }

    /**
     * @return the lastError
     */
    public String getLastError() {
        return lastError;
    }

    /**
     * @param lastError the lastError to set
     */
    public void setLastError(String lastError) {
        this.lastError = lastError;
    }
}
